package unilearn.da;

import java.sql.*;
import java.util.Vector;

import unilearn.domain.Assignment;

/**
 * @author David Oliver, 4084551
 * @author Ben Morrall. 4158768
 * @version 0.1, Apr 30, 2008, initial creation, dro
 * @version 0.2, Apr 30, 2008, base insert/get functions added, dro
 * @version 0.3, Apr 30, 2008, added delete/update functions, dro
 * @version 0.4, May 1, 2008, added null check to ps var, blm
 * @version 0.5, May 1, 2008, fixed bad sql query, blm
 */
public class DAAssignment extends DABase
{
	public DAAssignment()
	{
	}

	public static long insertAssignment(Assignment assign) throws SQLException
	{
		long assID = 0;
		PreparedStatement ps = null;

		try
		{
			Connection conn = getConn();

			ps = conn.prepareStatement(
					"INSERT INTO ASSIGNMENT (assignment_name,assignment_description,due_date,accepting_submissions,release_date,subject_id,file_id) "
							+ "VALUES (?,?,?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS);

			ps.setString(1, assign.getAssignmentName());
			ps.setString(2, assign.getAssignmentDescription());
			ps.setDate(3, assign.getAssignmentDueDate());
			ps.setBoolean(4, assign.getAssignmentAcceptingSubmissions());
			ps.setDate(5, assign.getAssignmentReleaseDate());
			ps.setLong(6, assign.getAssignmentSubjectID());
			ps.setLong(7, assign.getAssignmentFileID());

			ps.execute();

			ResultSet results = ps.getGeneratedKeys();
			if (results.next())
			{
				assID = results.getLong(1);
			}
		}
		finally
		{
			if (ps != null) ps.close();
		}

		return assID;
	}

	public static Assignment getAssignmentByID(long assignmentID) throws SQLException
	{
		Assignment assign = null;
		PreparedStatement ps = null;

		try
		{
			Connection conn = getConn();

			ps = conn.prepareStatement("select * from assignment where assignment_id = ?");
			ps.setLong(1, assignmentID);

			ResultSet rs = ps.executeQuery();

			if (rs.next())
			{
				assign = new Assignment(rs.getLong("assignment_id"));

				assign.setAssignmentName(rs.getString("assignment_name"));
				assign.setAssignmentDescription(rs.getString("assignment_description"));
				assign.setAssignmentDueDate(rs.getDate("dueDate"));
				assign.setAssignmentAcceptingSubmissions(rs.getBoolean("accepting_submissions"));
				assign.setAssignmentReleaseDate(rs.getDate("release_date"));
				assign.setAssignmentSubjectID(rs.getLong("subject_id"));
				assign.setAssignmentFileID(rs.getLong("file_id"));
			}

		}
		finally
		{
			if (ps != null) ps.close();
		}

		return assign;
	}

	public static Vector<Assignment> getAssignmentBySubject(long subjectID)
		throws SQLException
	{
		Vector<Assignment> assign = new Vector<Assignment>();
		PreparedStatement ps = null;

		try
		{
			Connection conn = getConn();

			ps = conn.prepareStatement("select * from assignment where subject_id = ?");
			ps.setLong(1, subjectID);

			ResultSet rs = ps.executeQuery();

			while (rs.next())
			{
				assign.add(getAssignmentByID(rs.getLong("assignment_id")));
			}

		}
		finally
		{
			if (ps != null) ps.close();
		}

		return assign;
	}

	public static void updateAssignment(long assignmentID, Assignment assign)
		throws SQLException
	{
		PreparedStatement ps = null;

		try
		{
			Connection conn = getConn();

			ps = conn.prepareStatement("UPDATE ASSIGNMENT SET assignment_name = ?, assignment_description = ?, due_date = ?, "
					+ "accepting_submissions = ?, release_date = ?, subject_id = ? , file_id = ? WHERE assignment_id = ?");

			ps.setString(1, assign.getAssignmentName());
			ps.setString(2, assign.getAssignmentDescription());
			ps.setDate(3, assign.getAssignmentDueDate());
			ps.setBoolean(4, assign.getAssignmentAcceptingSubmissions());
			ps.setDate(5, assign.getAssignmentReleaseDate());
			ps.setLong(6, assign.getAssignmentSubjectID());
			ps.setLong(7, assign.getAssignmentFileID());
			ps.setLong(8, assignmentID);

			ps.execute();

		}
		finally
		{
			if (ps != null) ps.close();
		}

		// TODO: possibly return a confirmation.
	}

	public static void deleteAssignment(long assignmentID) throws SQLException
	{
		PreparedStatement ps = null;

		try
		{
			Connection conn = getConn();

			ps = conn.prepareStatement("DELETE FROM ASSIGNMENT WHERE assignment_id = ?");
			ps.setLong(1, assignmentID);

			ps.execute();
		}
		finally
		{
			if (ps != null) ps.close();
		}
	}
}
